﻿using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace S.EliminationOrDath.Infrastructure
{
    public class DBHelper
    {
        private string ConnetionString = "";
        public DBHelper(IConfiguration configuration)
        {
            ConnetionString = configuration.GetValue<string>("ConnectionString");
        }
        /// <summary>
        /// 公共的查询
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public async Task<DataSet> DataSet(string sqlCmd, SqlParameter[] values = null)
        {
            SqlConnection con;
            using (con = new SqlConnection(ConnetionString))
            {
                try
                {
                    con.Open();
                    SqlCommand command = new SqlCommand(sqlCmd, con);//执行sql命令对象，传入要执行的sql语句和数据连接对象
                    if (values != null)
                    {
                        command.Parameters.AddRange(values);
                    }
                    //数据适配器
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    //创建数据集，将适配器查询回来的数据放入数据集中
                    DataSet set = new DataSet();
                    adapter.Fill(set);

                    return await Task.FromResult(set);
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    //关闭连接
                    con.Close();
                }
            }
        }
        /// <summary>
        /// 公共的增删改反回改变行的Id
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public int ExecuteScalar(string sqlCmd, SqlParameter[] values = null)
        {
            SqlConnection con;
            using (con = new SqlConnection(ConnetionString))
            {
                try
                {
                    con.Open();
                    StringBuilder stringBuilder = new StringBuilder();
                    SqlCommand command = new SqlCommand(sqlCmd, con);
                    if (values != null)
                    {
                        command.Parameters.AddRange(values);
                    }
                    int db = Convert.ToInt32(command.ExecuteScalar());
                    return db;
                }
                catch (Exception)
                {

                    throw;
                }
                finally
                {
                    con.Close();
                }
            }
        }
        /// <summary>
        /// 公共的增删改反回受影响的行数
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public async Task<int> ExecuteNonQuery(string sqlCmd, SqlParameter[] values = null)
        {
            SqlConnection con;
            using (con = new SqlConnection(ConnetionString))
            {
                try
                {
                    con.Open();
                    StringBuilder stringBuilder = new StringBuilder();
                    SqlCommand command = new SqlCommand(sqlCmd, con);
                    if (values != null)
                    {
                        command.Parameters.AddRange(values);
                    }
                    int db = await command.ExecuteNonQueryAsync();
                    return db;
                }
                catch (Exception)
                {

                    throw;
                }
                finally
                {
                    con.Close();
                }
            }
        }
        /// <summary>
        /// 公共的增删改反回受影响的行数事务版
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public async Task<bool> ExecuteNonQueryTran(List<string> sqlCmd, List<SqlParameter[]> values = null)
        {
            using (SqlConnection con = new SqlConnection(ConnetionString))
            {
                await con.OpenAsync();
                SqlTransaction transaction = con.BeginTransaction();
                bool yn = false;

                try
                {
                    StringBuilder stringBuilder = new StringBuilder();
                    for (int i = 0; i < sqlCmd.Count; i++)
                    {
                        SqlCommand command = new SqlCommand(sqlCmd[i], con, transaction);
                        if (values[i] != null)
                        {
                            command.Parameters.AddRange(values[i]);
                        }
                        await command.ExecuteNonQueryAsync();
                    }

                    await transaction.CommitAsync();
                    yn = true;
                }
                catch (Exception)
                {
                    await transaction.RollbackAsync();
                    throw;
                }
                finally
                {
                    con.Close();
                }
                return yn;
            }
        }

        /// <summary>
        /// 将DataTable转List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        public List<T> ConvertDataTableToList<T>(DataTable dataTable) where T : class, new()
        {
            //创建泛型集合
            List<T> list = new List<T>();
            //循环传回来的表格的行
            foreach (DataRow row in dataTable.Rows)
            {
                //实例化集合
                T obj = new T();

                //循环每行里的每列
                foreach (DataColumn col in dataTable.Columns)
                {
                    //属性信息，获取集合类型和每列的属性
                    PropertyInfo propertyInfo = obj.GetType().GetProperty(col.ColumnName);
                    //判断属性信息和行信息是否为空
                    if (propertyInfo != null && row[col] != DBNull.Value)
                    {
                        propertyInfo.SetValue(obj, row[col], null);
                    }
                }
                list.Add(obj);

            }
            return list;
        }
        /// <summary>
        /// 公共查询方法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlCmd"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public async Task<List<T>> GetListDataBySqlWhere<T>(string sqlCmd, SqlParameter[] values = null) where T : class, new()
        {
            //获取查询方法
            DataSet set = await DataSet(sqlCmd, values);
            //返回转换过的查询方法
            return ConvertDataTableToList<T>(set.Tables[0]);
        }
    }
}
